var express = require('express');
var router = express.Router();
var {customQuery} = require('../mysql/index')


// 查询 link_type 表
router.get('/type', function(req, res, next) {
  const sql = `SELECT * FROM link_type`;
  customQuery(sql)
    .then(results => {
      return res.status(200).send({ code: 200, msg: 'link_type 查询成功', list: results });
    })
    .catch(error => {
      return res.status(500).send({ code: 500, msg: '查询 link_type 时出错', error: error });
    });
});
// 增加 link_type
router.post('/type/add', function(req, res, next) {
  const { name } = req.body;
  const sql = `INSERT INTO link_type (name) VALUES ('${name}')`;
  customQuery(sql)
    .then(results => {
      return res.status(200).send({ code: 200, msg: 'link_type 添加成功', list: results });
    })
    .catch(error => {
      return res.status(500).send({ code: 500, msg: '添加 link_type 时出错', error: error });
    });
});
// 删除 link_type
// 删除 link_type
router.post('/type/delete', function(req, res, next) {
  const { id } = req.body;
  const checkSql = `SELECT COUNT(*) AS count FROM link WHERE link_type = '${id}'`;
  customQuery(checkSql)
    .then(result => {
      if (result[0].count > 0) {
        return res.status(200).send({ code: 400, msg: '该类型被引用，无法删除' });
      } else {
        const deleteSql = `DELETE FROM link_type WHERE id = '${id}'`;
        customQuery(deleteSql)
          .then(results => {
            return res.status(200).send({ code: 200, msg: 'link_type 删除成功', list: results });
          })
          .catch(error => {
            return res.status(200).send({ code: 500, msg: '删除 link_type 时出错', error: error });
          });
      }
    })
    .catch(error => {
      return res.status(200).send({ code: 500, msg: '查询 link 表时出错', error: error });
    });
});
// 更新 link_type
// 更新 link_type
router.post('/type/update', function(req, res, next) {
  const { id, name } = req.body;
  const sql = `UPDATE link_type SET name = '${name}' WHERE id = '${id}'`;
  customQuery(sql)
    .then(results => {
      return res.status(200).send({ code: 200, msg: 'link_type 更新成功', list: results });
    })
    .catch(error => {
      return res.status(200).send({ code: 500, msg: '更新 link_type 时出错', error: error });
    });
});



// 向链接表中添加新链接
router.post('/add', function(req, res, next) {
  const { title, type, url, logo_url, link_type, description } = req.body;
  const sql = `INSERT INTO link (title, type, url, logo_url, link_type, description) VALUES ('${title}', '${type}', '${url.replace(/[\\]/g, '\\\\')}', '${logo_url.replace(/[\\]/g, '\\\\')}', '${link_type}', '${description}')`;
  customQuery(sql)
    .then(results => {
      return res.status(200).send({ code: 200, msg: '链接添加成功', list: results });
    })
    .catch(error => {
      return res.status(500).send({ code: 500, msg: '添加链接时出错', error: error });
    });
});

// 更新链接
router.post('/update', function(req, res, next) {
  const { id, title, type, url, logo_url, link_type, description } = req.body;
   const sql = `UPDATE link SET title = '${title}', type = '${type}', url = '${url ? url.replace(/[\\]/g, '\\\\') : ''}', logo_url = '${logo_url ? logo_url.replace(/[\\]/g, '\\\\') : ''}', link_type = '${link_type}', description = '${description}' WHERE id = '${id}'`;
  customQuery(sql)
      .then(results => {
        console.log(11111111)
        return res.status(200).send({ code: 200, msg: '链接更新成功', list: results });
      })
      .catch(error => {
        console.log(error)
        return res.status(200).send({ code: 500, msg: '更新链接时出错', error: error });
      });
});



// 删除链接
router.post('/delete', function(req, res, next) {
  const { id } = req.body;
  const sql = `DELETE FROM link WHERE id = '${id}'`;
  customQuery(sql)
    .then(results => {
      return res.status(200).send({ code: 200, msg: '链接删除成功', list: results });
    })
    .catch(error => {
      return res.status(500).send({ code: 500, msg: '删除链接时出错', error: error });
    });
});


// 查询链接表
router.get('/', function(req, res, next) {
  const { page = 1, limit = 10, type, link_type, key } = req.query;
  let sql = `SELECT * FROM link`;
  let conditions = [];
  if (type) {
    conditions.push(`type = '${type}'`);
  }
  if (link_type) {
    conditions.push(`link_type = '${link_type}'`);
  }
  if (key) {
    conditions.push(`(title LIKE '%${key}%' OR description LIKE '%${key}%')`);
  }
  if (conditions.length > 0) {
    sql += ` WHERE ${conditions.join(' AND ')}`;
  }
  const offset = (page - 1) * limit;
  sql += ` LIMIT ${offset}, ${limit}`;
  console.log('sql',sql)
  customQuery(sql)
    .then(results => {
      const data = { list: results };
      if (type || link_type || key) {
        return customQuery(`SELECT COUNT(*) AS total FROM link WHERE ${conditions.join(' AND ')}`)
          .then(countResult => {
            data.total = countResult[0].total;
            return res.status(200).send({ code: 200, msg: '链接查询成功', data });
          });
      } else {
        return customQuery('SELECT COUNT(*) AS total FROM link')
          .then(countResult => {
            data.total = countResult[0].total;
            return res.status(200).send({ code: 200, msg: '链接查询成功', data });
          });
      }
    })
    .catch(error => {
      return res.status(500).send({ code: 500, msg: '查询链接时出错', error: error });
    });
});

// 根据关键字和链接类型查询链接
router.get('/key', function(req, res, next) {
  const { key, link_type } = req.query;
  console.log('jinlai了key', key);
  let sql = `SELECT * FROM link WHERE title LIKE '%${key}%' OR description LIKE '%${key}%'`;
  if (link_type) {
    sql += ` AND link_type = '${link_type}'`;
  }
  customQuery(sql)
    .then(results => {
      return res.status(200).send({ code: 200, msg: '链接查询成功', list: results });
    })
    .catch(error => {
      return res.status(500).send({ code: 500, msg: '查询链接时出错', error: error });
    });
});

module.exports = router;



